#!/usr/bin/env python
# -*- coding: UTF-8 -*-
'''
@Project ：图灵学习 
@File    ：7.腾讯招聘mysql.py
@IDE     ：PyCharm 
@Author  ：ChenLiGao
@Date    ：2024-11-29 15:12
'''
import time

import pymysql
import requests
class TxWork:
    def __init__(self):
        self.url = 'https://careers.tencent.com/tencentcareer/api/post/Query?timestamp=1732864241716&countryId=&cityId=&bgIds=&productId=&categoryId=&parentCategoryId=&attrId=1&keyword=python&pageIndex={}&pageSize=10&language=zh-cn&area='
        self.headers = {
                'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/131.0.0.0 Safari/537.36'
            }

        self.db = pymysql.Connect(host="localhost", user="root", password="123456", db="py_spider")
        self.cursor = self.db.cursor()

    def __del__(self):
        print("数据库关闭")
        self.cursor.close()
        self.db.close()
    def get_work_info(self):
        """
        获取工作信息
        :return:
        """
        for page in range(1, 5):
            page_url = self.url.format(page)
            response = requests.get(page_url, headers=self.headers).json()
            work_list = response['Data']['Posts']
            yield work_list

    def create_table(self):
        """
            创建一个数据库表
        :return:
        """
        sql = """
        create table if not exists tx_work(
                id int primary key auto_increment,
                work_name varchar(100) not null,
                country_name varchar(50),
                city_name varchar(50),
                work_desc text
            );
        """
        try:
            self.cursor.execute(sql)
            print("创建表成功")
        except Exception as e:
            print("创建表失败：", e)

    def inset_work_info(self, *args):
       """
       :param args:
         id
         work_name
         country_name
         city_name
         work_desc
       :return:
       """
       sql = """
            insert into tx_work values (%s, %s, %s, %s, %s);
       """
       print(args)
       try:
            self.cursor.execute(sql, args);
            self.db.commit()
            print("数据插入成功")
       except Exception as e:
            print("数据插入失败：", e)
            # 失败后需要进行事务回滚
            self.db.rollback()

    def main(self):
        self.create_table()
        al_work_generator = self.get_work_info()
        work_id = 0
        for work_list in al_work_generator:
            for work_info in work_list:
                work_name = work_info["RecruitPostName"]
                country_name = work_info["CountryName"]
                city_name = work_info["LocationName"]
                work_desc = work_info["Responsibility"]
                self.inset_work_info(work_id, work_name, country_name, city_name, work_desc)

if __name__ == '__main__':
    txwork = TxWork()
    txwork.main()



